package main

import (
	"database/sql/driver"
	"errors"
	"fmt"
	"strings"

	_ "github.com/go-sql-driver/mysql"
	"github.com/jmoiron/sqlx"
)

var db *sqlx.DB

type User struct {
	ID   int
	Age  int
	Name string
}

func initDB() (err error) {
	dsn := "root:king+5688@tcp(127.0.0.1:5690)/sql_test?charset=utf8mb4&parseTime=True"
	db, err = sqlx.Connect("mysql", dsn) // 链接数据库 = Open和Ping
	if err != nil {
		fmt.Println("connect DB failed,err:", err)
		return err
	}
	db.SetMaxOpenConns(20) // 设置最大链接数
	db.SetMaxIdleConns(10) // 设置空闲最大连接数
	return
}

// 查询单行数据
func queryRowDemo() {
	sqlStr := "select id,name,age from user where id=?"
	var u User
	err := db.Get(&u, sqlStr, 1)
	if err != nil {
		fmt.Println("get failed,err:", err)
		return
	}
	fmt.Printf("id:%d name:%s age:%d\n", u.ID, u.Name, u.Age)
}

// 查询多行数据
func queryMultiRowDemo() {
	sqlStr := "select id, name, age from user where id > ?"
	var users []User
	err := db.Select(&users, sqlStr, 0)
	if err != nil {
		fmt.Printf("query failed, err:%v\n", err)
		return
	}
	fmt.Printf("users:%#v\n", users)
}

// 插入数据
func insertRowDemo() {
	sqlStr := "insert into user(name, age) values (?,?)"
	ret, err := db.Exec(sqlStr, "沙河小王子", 19)
	if err != nil {
		fmt.Printf("insert failed, err:%v\n", err)
		return
	}
	theID, err := ret.LastInsertId() // 新插入数据的id
	if err != nil {
		fmt.Printf("get lastinsert ID failed, err:%v\n", err)
		return
	}
	fmt.Printf("insert success, the id is %d.\n", theID)
}

// 更新数据
func updateRowDemo() {
	sqlStr := "update user set age=? where id = ?"
	ret, err := db.Exec(sqlStr, 39, 6)
	if err != nil {
		fmt.Printf("update failed, err:%v\n", err)
		return
	}
	n, err := ret.RowsAffected() // 操作影响的行数
	if err != nil {
		fmt.Printf("get RowsAffected failed, err:%v\n", err)
		return
	}
	fmt.Printf("update success, affected rows:%d\n", n)
}

// 删除数据
func deleteRowDemo() {
	sqlStr := "delete from user where id = ?"
	ret, err := db.Exec(sqlStr, 6)
	if err != nil {
		fmt.Printf("delete failed, err:%v\n", err)
		return
	}
	n, err := ret.RowsAffected() // 操作影响的行数
	if err != nil {
		fmt.Printf("get RowsAffected failed, err:%v\n", err)
		return
	}
	fmt.Printf("delete success, affected rows:%d\n", n)
}

// NamedExec 方法绑定SQL语句与结构体或map中的同名字段  增删改
func inserUserDemo() (err error) {
	sqlStr := "INSERT INTO user(name,age) VALUES(:name,:age)"
	_, err = db.NamedExec(sqlStr, map[string]interface{}{
		"name": "强强",
		"age":  36,
	})
	return
}

// 与DB.NamedExec同理，这里是支持查询
func namedQuery() {
	sqlStr := "SELECT * FROM user WHERE name=:name"
	// 使用map做命名查询
	rows, err := db.NamedQuery(sqlStr, map[string]interface{}{"name": "强强"})
	if err != nil {
		fmt.Printf("db.NamedQuery failed, err:%v\n", err)
		return
	}
	defer rows.Close()
	for rows.Next() {
		var u User
		err := rows.StructScan(&u)
		if err != nil {
			fmt.Printf("scan failed, err:%v\n", err)
			continue
		}
		fmt.Printf("user:%#v\n", u)
	}

	u := User{
		Name: "七米",
	}
	// 使用结构体命名查询，根据结构体字段的 db tag进行映射
	rows, err = db.NamedQuery(sqlStr, u)
	if err != nil {
		fmt.Printf("db.NamedQuery failed, err:%v\n", err)
		return
	}
	defer rows.Close()
	for rows.Next() {
		var u User
		err := rows.StructScan(&u)
		if err != nil {
			fmt.Printf("scan failed, err:%v\n", err)
			continue
		}
		fmt.Printf("user:%#v\n", u)
	}
}

// 事务操作 可以使用sqlx中提供的db.Beginx()和tx.Exec()方法
func transactionDemo() (err error) {
	tx, err := db.Beginx()
	if err != nil {
		fmt.Println("begin trrans failed,err:", err)
		return err
	}
	defer func() {
		if p := recover(); p != nil {
			tx.Rollback()
			panic(p) // re-throw panic after Rollback
		} else if err != nil {
			fmt.Println("rollback")
			tx.Rollback() // err is non-nil; don't change it
		} else {
			err = tx.Commit() // err is nil; if Commit returns error update err
			fmt.Println("commit")
		}
	}()

	sqlStr1 := "update user set age=age+2 where id=?"
	rs, err := tx.Exec(sqlStr1, 4)
	if err != nil {
		return err
	}
	n, err := rs.RowsAffected()
	if err != nil {
		return err
	}
	if n != 1 {
		return errors.New("exec sqlStr1 failed")
	}

	sqlStr2 := "update user set age=age-2 where id=?"
	rs, err = tx.Exec(sqlStr2, 5)
	if err != nil {
		return err
	}
	n, err = rs.RowsAffected()
	if err != nil {
		return err
	}
	if n != 1 {
		return errors.New("exec sqlStr2 failed")
	}
	return err
}

type user struct {
	Name string `db:"name"`
	Age  int    `db:"age"`
}

// 自己拼接语句实现 批量插入
func BatchInsertUsers(users []*user) error {
	// 存放 (?,?) 的slice
	valueStrings := make([]string, 0, len(users))
	// 存放values的slice
	valueArgs := make([]interface{}, 0, len(users)*2)
	// 遍历users准备相关数据
	for _, u := range users {
		valueStrings = append(valueStrings, "(?,?)")
		valueArgs = append(valueArgs, u.Name)
		valueArgs = append(valueArgs, u.Age)
	}
	// 自行拼接要执行的具体语句
	stmt := fmt.Sprintf("insert into user(name,age) values %s", strings.Join(valueStrings, ","))
	_, err := db.Exec(stmt, valueArgs...)
	return err
}

// sqlx.In 批量插入  前提是需要结构体实现driver.Value接口
func (u user) Value() (driver.Value, error) {
	return []interface{}{u.Name, u.Age}, nil
}
func BatchInsertUsers2(users []interface{}) error {
	query, args, _ := sqlx.In(
		"INSERT INTO user (name, age) VALUES (?), (?), (?)",
		users..., // 如果arg实现了 driver.Valuer, sqlx.In 会通过调用 Value()来展开它
	)
	fmt.Println(query) // 查看生成的querystring
	fmt.Println(args)  // 查看生成的args
	_, err := db.Exec(query, args...)
	return err
}

// NamedExec 实现批量插入
func BatchInsertUsers3(users []*user) error {
	_, err := db.NamedExec("insert into user (name,age) values (:name,:age)", users)
	return err
}

// in 查询  查询id在给定id集合中的数据
func queryByIds(ids []int) (users []user, err error) {
	query, args, err := sqlx.In("select name,age from user where id in (?)", ids)
	if err != nil {
		return
	}
	// sqlx.In 返回带`?`的查询语句，使用Rebind方法重新绑定它
	query = db.Rebind(query)
	err = db.Select(&users, query, args...)
	return
}

func main() {
	err := initDB()
	if err != nil {
		fmt.Println(err)
		return
	}
	// queryRowDemo()
	// queryMultiRowDemo()
	// insertRowDemo()
	// updateRowDemo()
	// deleteRowDemo()

	// inserUserDemo()
	// namedQuery()

	// transactionDemo()

	// u := []*user{
	// 	{Name: "张山", Age: 13},
	// 	{Name: "李斯", Age: 14},
	// 	{Name: "王武", Age: 15},
	// }
	// BatchInsertUsers(u)

	// u2 := []interface{}{
	// 	user{Name: "张山", Age: 13},
	// 	user{Name: "李斯", Age: 14},
	// 	user{Name: "王武", Age: 15},
	// }
	// err = BatchInsertUsers2(u2)
	// if err != nil {
	// 	fmt.Println("BatchInsertUsers2 failed, err", err)
	// }

	// u1 := user{Name: "张山", Age: 13}
	// u2 := user{Name: "李斯", Age: 14}
	// user3 := []*user{&u1, &u2}
	// err = BatchInsertUsers3(user3)
	// if err != nil {
	// 	fmt.Printf("BatchInsertUsers3 failed, err:%v\n", err)
	// }

	user, _ := queryByIds([]int{1, 2, 3, 7})
	fmt.Println(user)

}
